use master
drop database Mentor
go
---CREATE DB
create database Mentor
go

use Mentor
go

create table UserType
(
	UserTypeID		int				identity,
	Name				nvarchar(30),
	LastModDate         datetime2,
    CreatedDate         datetime2		not null	
)
go
alter table UserType
   add constraint PK_UserType primary key (UserTypeID)
go

--CREATE TABLES
create table MeMaUser
(
	MeMaUserID			int				identity,
	UserTypeID		int				not null,
	FirstName		nvarchar(100)	not null,
	LastName		nvarchar(100)	not null,
	Email				nvarchar(30)	not null,
	Password			nvarchar(30)	not null,
	IsActive			bit,
    LastModDate         datetime2,
    CreatedDate         datetime2		not null	
)
go
alter table MeMaUser
   add constraint PK_MeMaUser primary key (MeMaUserID)
go

create table MentorStatusType
(
	MentorStatusTypeID		int				identity,
	Name				nvarchar(30),
	LastModDate         datetime2,
    CreatedDate         datetime2		not null	
)
go
alter table MentorStatusType
   add constraint PK_MentorStatusType primary key (MentorStatusTypeID)
go

create table UserProfile
( 
	UserProfileID	int				identity,
	MeMaUserID		nvarchar(100)	not null, 
	Picture			image,
	MentorStatusTypeID int,
	MentorRating int, 
	LinkedInLink nvarchar(1000),
	FacebookLink 	nvarchar(1000),
	TwitterHandle 	nvarchar(1000),
	PersonalWebsite	nvarchar(1000),
	Summary	nvarchar(max),
	LastModDate         datetime2,
    CreatedDate         datetime2		not null		
)
go
alter table UserProfile
   add constraint PK_User primary key (UserProfileID)
go


create table MentorMentee
( 
	MentorMenteeID		int			identity,
	MentorID			int	not null, 
	MenteeID			int	not null, 	
	AcceptedByMentorDateTime datetime2,
	RequestedByMenteeDateTime datetime2,
	MatchValue			int,
    LastModDate         datetime2,
    CreatedDate         datetime2		not null	
)
go
alter table MentorMentee
   add constraint PK_MentorMentee primary key (MentorMenteeID)
go

create table MentorMatchTag
(
	MentorMatchTagID		int				identity,
	Name				nvarchar(30),
	LastModDate         datetime2,
    CreatedDate         datetime2		not null	
)
go
alter table MentorMatchTag
   add constraint PK_MentorMatchTag primary key (MentorMatchTagID)
go
create table UserMentorTag
( 
	
	UserMentorTagID int identity,
	MeMaUserID			int				not null,
	MentorMatchTagID	int				not null, 
	LastModDate         datetime2,
    CreatedDate         datetime2		not null	
)
go
alter table UserMentorTag
   add constraint PK_UserMentorTag primary key (UserMentorTagID)
go

/*
go
create table Communication
( 
	CommunicationID		int				not null,
	MentorMenteeID		44
	A int	not null, 
	MenteeID			int	not null, 	
    LastModDate         datetime2,
    CreatedDate         datetime2		not null	
)
go
alter table MentorMentee
   add constraint PK_MentorMentee primary key (MentorMenteeID)
go
*/